package Model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Information.CV;

public class CVDAO{
	int CVNO;
	private String UUser;
	private short USex;
	private String UEmail;
	private int UTel;
	private String UAddress;
	private int UPostCode;
	private String Education;
	private String Speciality;
	private String JType;
	private String Job;
	private String City;
	private int Expectation;
	private String SelfIntroduction;
	private Date PublishTime;
	private int TotalClick;
	
	public CVDAO()
	{
		this.CVNO=0;
		this.UUser = "";
		this.USex = 1;
		this.UEmail = "";
		this.UTel = 0;
		this.UAddress = "";
		this.UPostCode = 0;
		this.Education = "";
		this.Speciality = "";
		this.JType = "";
		this.Job = "";
		this.City = "";
		this.Expectation = 0;
		this.SelfIntroduction = "";
		this.PublishTime = new Date(0);
		this.TotalClick=0;
	}
	
	public boolean AddNewCV(String UUser,short USex,String UEmail,int UTel,String UAddress,int UPostCode,String Education,String Speciality,String JType,String Job,String City,int Expectation,String SelfIntroduction) throws SQLException, ClassNotFoundException
	{
		String sql ="insert into CV (UUser,USex,UEmail,UTel,UAddress,UPostCode,Education,Speciality,JType,Job,City,Expectation,SelfIntroduction) values('"+UUser+"','"+USex+"','"+UEmail+"','"+UTel+"','"+UAddress+"','"+UPostCode+"','"+Education+"','"+Speciality+"','"+JType+"','"+Job+"','"+City+"','"+Expectation+"','"+SelfIntroduction+"')";
		Connection con = null;
		Statement stmt = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		if(stmt.executeUpdate(sql)==1)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	/*
	public ArrayList<CV> SeeCVByCVNO(int CVNO) throws SQLException, ClassNotFoundException
	{
		ArrayList<CV> arrayList = null;
		CV CV = new CV();
		String sql ="select * from CV where CVNO ='"+CVNO+"'";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		result = stmt.executeQuery(sql);

		UUser = result.getString("UUser");
		USex=result.getShort("USex");
		UEmail=result.getString("UEmail");
		UTel=result.getInt("UTel");
		UAddress=result.getString("UAddress");
		UPostCode=result.getInt("UPostCode");
		Education=result.getString("Education");
		Speciality=result.getString("Speciality");
		JType=result.getString("JType");
		Job=result.getString("Job");
		City=result.getString("City");
		Expectation=result.getInt("Expectation");
		SelfIntroduction=result.getString("SelfIntroduction");
		PublishTime=result.getDate("PublishTime");
		TotalClick=result.getInt("TotalClick");
		CV.SetCVInfo(CVNO,UUser,USex,UEmail,UTel,UAddress,UPostCode,Education,Speciality,JType,Job,City,Expectation,SelfIntroduction);
		arrayList.add(CV);
		return arrayList;
	}*/
	
	public ArrayList<CV> SeeCVByUUser(String UUser) throws SQLException, ClassNotFoundException
	{
		ArrayList<CV> arrayList = new ArrayList<CV>();
		String sql ="select * from CV where UUser ='"+UUser+"'";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		result = stmt.executeQuery(sql);
		while(result.next())
		{
			CV cv = new CV();
			CVNO = result.getInt("CVNO");
			UUser = result.getString("UUser");
			USex=result.getShort("USex");
			UEmail=result.getString("UEmail");
			UTel=result.getInt("UTel");
			UAddress=result.getString("UAddress");
			UPostCode=result.getInt("UPostCode");
			Education=result.getString("Education");
			Speciality=result.getString("Speciality");
			JType=result.getString("JType");
			Job=result.getString("Job");
			City=result.getString("City");
			Expectation=result.getInt("Expectation");
			SelfIntroduction=result.getString("SelfIntroduction");
			PublishTime=result.getDate("PublishTime");
			TotalClick=result.getInt("TotalClick");
			cv.SetCVInfo(CVNO,UUser,USex,UEmail,UTel,UAddress,UPostCode,Education,Speciality,JType,Job,City,Expectation,SelfIntroduction);
			arrayList.add(cv);
		}
		return arrayList;
	}
	
	public boolean DeleteCVByCVNO(int CVNO) throws ClassNotFoundException, SQLException
	{
		String sql ="delete from CV where CVNO='"+CVNO+"'";
		Connection con = null;
		Statement stmt = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		if(stmt.executeUpdate(sql)==1)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	
	public boolean UpdateCV(int CVNO,String UUser,short USex,String UEmail,int UTel,String UAddress,int UPostCode,String Education,String Speciality,String JType,String Job,String City,int Expectation,String SelfIntroduction) throws ClassNotFoundException, SQLException
	{
		String sql ="update CV set UUser='"+UUser
				+"',set USex='"+USex
				+"',set UEmail='"+UEmail
				+"',set UTel='"+UTel
				+"',set UAddress='"+UAddress
				+"',set UPostCode='"+UPostCode
				+"',set Education='"+Education
				+"',set Speciality='"+Speciality
				+"',set JType='"+JType
				+"',set Job='"+Job
				+"',set City='"+City
				+"'set Expectation='"+Expectation
				+"',set SelfIntroduction='"+SelfIntroduction
				+"'where CVNO='"+CVNO+"')";
		Connection con = null;
		Statement stmt = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		if(stmt.executeUpdate(sql)==1)
		{
			return true;//update successfully
		}
		else
		{
			return false;//update failed
		}
	}
}